from flask import current_app as app, jsonify
from  flask import Blueprint,request,make_response
import os
from utils.myfile import myf
from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
from views.mydb import db1
from views.getdata import getdata

bp_comany = Blueprint("comany",__name__)


@bp_comany.route('/test')
async def test():
    str="请求ip:"+request.remote_addr+',请求的url'+request.url+'错误信息'+'\n'
    app.logger.error(str)
    return "hello word"
@bp_comany.route('/upload',methods=["POST"])
def upload():
    # 获取文件
    file = request.files['file']

    newname = myf.rename(file.filename)

    # 上传文件
    file.save(os.path.join('./static/files/' + newname))
    # 打开文件读取
    name = './static/files/' + newname
    bk = open_workbook(name,encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(1,nrows):
        row_date = sh.row_values(i)
        print(row_date[0])
        print(row_date[1])
    return 'ok'


@bp_comany.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    table.write(0, 0, "资产编号")
    table.write(0, 1, "资产名称")

    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file


def xtree(data):
    if not data:
        return data
    lst=[]
    tree={}
    for i in data:
        i["label"] = i["name"]
    for i in data:
        tree[i["id"]]=i
    for j in data:
        pid = j["pid"]
        if pid == 0:
            lst.append(j)
        else:
            if "children" not in tree[pid]:
                tree[pid]["children"] = []
            tree[pid]["children"].append(j)
    return lst


@bp_comany.route("/deptlist")
def deptlist():
    sql = "select * from dept"
    res = db1.find_all(sql)
    list = xtree(res)
    return jsonify({"code":200,"list":list})

@bp_comany.route("/adddept",methods=["POST"])
def adddept():
    res = getdata()
    sql = "insert into dept values(0,'%s','%s','%d')"%(res["name"],res["code"],int(res["pid"]))
    db1.update(sql)
    return jsonify({"code":200})

# from utils.es import Es
# @bp_comany.route("/deptesinsert")
# def deptesinsert():
#     es=Es("testes","test-type")
#     #插入数据
#     sql="select * from dept"
#     res=db1.find_all(sql)
#     for i in res:
#         es.insert(i)
#     return "ok"
# @bp_comany.route("/getdata")
# def getdata():
#     es=Es("testes","test-type")
#     query={"match":{"name":"开发"}}
#     #插入数据
#     res = es.find(query)
#     print(res)
#     return "ok"

